﻿using DataAccess;
using DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAO
{
    public class NhanVienDAO
    {
        public static String TaoMaNhanVien()
        {
            String strChu = "NV";
            String strSo = "0";
            String strMaDau = "NV001";

            String strSql = "SELECT TOP 1 MaNhanVien FROM NHANVIEN ORDER BY MaNhanVien DESC";

            try
            {
                String strMaCuoi = DataConnection.ExecScalar(strSql).ToString();
                String strTemp = ((int.Parse(strMaCuoi.Remove(0, 2)) + 1)).ToString();
                String strMaNhanVien = strChu;
                while (strMaNhanVien.Length + strTemp.Length < 5)
                    strMaNhanVien += strSo;
                return strMaNhanVien + strTemp;

            }
            catch (Exception)
            {
                return strMaDau;
            }
        }
        public static int TiepNhanNhanVien(NhanVienDTO nv)
        {
            string query = "INSERT INTO NHANVIEN (MaNhanVien,TenNhanVien,DiaChi,Email,DienThoai,NgaySinh,NgayVaoLam,MatKhau)";
            query += "VALUES('" + nv.MaNhanVien + "',N'" + nv.TenNhanVien + "',N'" +nv.DiaChi + "','" + nv.EmailNV + "','" +nv.DienThoai + "','" + nv.NgaySinh + "','" + nv.NgayVaoLam + "','"+nv.MatKhau+"')";
            try
            {
                DataConnection.ExecQuery(query);
                return 1;
            }
            catch (System.Exception e)
            {
                Console.WriteLine("Not access to database:{0}", e.Message);
                return 0;
            }
        }
        public static List<NhanVienDTO> LoadDanhSachNhanVien()
        {
            List<NhanVienDTO> list = new List<NhanVienDTO>();
            string query = "SELECT * FROM NHANVIEN";
            DataTable dt = DataConnection.ExecQuery(query);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    NhanVienDTO nv = new NhanVienDTO();
                    nv.MaNhanVien = dr["MaNhanVien"].ToString();
                    nv.TenNhanVien = dr["TenNhanVien"].ToString();
                    nv.DiaChi = dr["DiaChi"].ToString();
                    nv.EmailNV = dr["Email"].ToString();
                    nv.DienThoai = dr["DienThoai"].ToString();
                    nv.NgaySinh = dr["NgaySinh"].ToString();
                    nv.NgayVaoLam = dr["NgayVaoLam"].ToString();
                    list.Add(nv);
                }
            }
            return list;
        }
        public static bool deleteNhanVien(NhanVienDTO nv)
        {
            string query = "DELETE FROM NHANVIEN WHERE MaNhanVien = '"+nv.MaNhanVien+"'";
            try
            {
                DataConnection.ExecQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                Console.WriteLine("Not access to database:{0}", e.Message);
                return false;
            }
        }
        public static bool ktDangNhap(string manhanvien,string matkhau)
        {
            string query = "SELECT MaNhanVien,MatKhau,MaChucVu FROM  NHANVIEN ";
            query += "WHERE (MaNhanVien = '"+ manhanvien +"') AND (MatKhau = '"+ matkhau +"')";
            if (DataConnection.ExecQuery(query).Rows.Count > 0)
            {
                return true;
            }
            return false;
        }

        public static DataTable DangNhap(string p1, string p2)
        {
            string query = "SELECT MaNhanVien,MatKhau,MaChucVu FROM  NHANVIEN ";
            query += "WHERE (MaNhanVien = '"+p1+"') AND (MatKhau = '"+p2+"')";
            DataTable dt = DataConnection.ExecQuery(query);
            return dt;
        }

        public static DataTable TimKiemDaiLyCoBan(string tenNhanVien)
        {
            string query = "Select * from NhanVien WHERE TenNhanVien like N'%" + tenNhanVien + "%'";
            DataTable dt = DataConnection.ExecQuery(query);
            return dt;
        }

        public static List<NhanVienDTO> LoadDanhSachNVCoMaCV()
        {
            List<NhanVienDTO> list = new List<NhanVienDTO>();
            string query = "SELECT * FROM NHANVIEN where MaChucVu is null";
            DataTable dt = DataConnection.ExecQuery(query);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    NhanVienDTO nv = new NhanVienDTO();
                    nv.MaNhanVien = dr["MaNhanVien"].ToString();
                    nv.TenNhanVien = dr["TenNhanVien"].ToString();
                    nv.DiaChi = dr["DiaChi"].ToString();
                    nv.EmailNV = dr["Email"].ToString();
                    nv.DienThoai = dr["DienThoai"].ToString();
                    nv.NgaySinh = dr["NgaySinh"].ToString();
                    nv.NgayVaoLam = dr["NgayVaoLam"].ToString();
                    list.Add(nv);
                }
            }
            return list;
        }
        public static DataTable MatKhauHienTai(string p)
        {
            string query = "SELECT MatKhau from NHANVIEN where MaNhanVien='"+p+"'";
            DataTable dt = DataConnection.ExecQuery(query);
            return dt;
        }
        public static bool UpDateMatKhau(NhanVienDTO nv, string maKhauMoi)
        {
            string query = "UPDATE NHANVIEN SET MatKhau ='" + maKhauMoi + "' where MaNhanVien='"+nv.MaNhanVien+"'";
            try
            {
                DataConnection.ExecNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                Console.WriteLine("Not access to database:{0}", e.Message);
                return false;
            }

        }

        public static bool UpDateChucVu(string maNhanVien, string maChucVu)
        {
            string query = "UPDATE NHANVIEN SET MaChucVu ='" + maChucVu + "' where MaNhanVien='"+maNhanVien+"'";
            try
            {
                DataConnection.ExecNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                Console.WriteLine("Not access to database:{0}", e.Message);
                return false;
            }
        }

        public static bool UpdateNhanVien(NhanVienDTO nv)
        {
            string query = "UPDATE NHANVIEN SET DiaChi ='" + nv.DiaChi + "',DienThoai ='" + nv.DienThoai + "',Email ='" + nv.EmailNV + "' where MaNhanVien='"+nv.MaNhanVien +"'";
            try
            {
                DataConnection.ExecNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                Console.WriteLine("Not access to database:{0}", e.Message);
                return false;
            }
        }

        public static DataTable TimKiemNangCao(NhanVienDTO dl, int tuoiTu, int tuoiDen, int namLamViecTu, int namLamViecDen)
        {

            string query = "Select * from NhanVien WHERE 1=1";

            if (tuoiTu != -500)
            {

                query += " YEAR(GETDATE())-YEAR(NgaySinh) >='" + tuoiTu + "'";
            }
            if (tuoiDen != -500)
            {

                query += " YEAR(GETDATE())-YEAR(NgaySinh) <='" + tuoiDen + "'";
            }
            if (namLamViecTu != -500)
            {

                query += " YEAR(GETDATE())-YEAR(NgaySinh) >='" + namLamViecTu + "'";
            }
            if (namLamViecDen != -500)
            {

                query += "YEAR(GETDATE())-YEAR(NgaySinh) <='" + namLamViecDen + "'";

            }
            if (dl.TenNhanVien != "")
            {
                query += " AND TenDaiLy like'%" + dl.TenNhanVien + "'OR TenDaiLy like'" + dl.TenNhanVien + "%'";
            }
            if (dl.DiaChi != "")
            {
                query += " AND DiaChi like'%" + dl.DiaChi + "'OR TenDaiLy like'" + dl.DiaChi + "%'";
            }
            if (dl.EmailNV != "")
            {
                query += " AND DiaChi like'%" + dl.EmailNV + "'OR TenDaiLy like'" + dl.EmailNV + "%'";
            }
            DataTable dt = DataConnection.ExecQuery(query);
            return dt;
        }
    }
}
